<?php
  include("DBConfig.class.php");

  class TambonChart{

    public static function getPovertyRatioChartByCode($proc,$disc){
      //Query Province  $votename, $country, $filter
      try {
        $db = new PDO("mysql:host=".DBConfig::$dbhost.";dbname=".DBConfig::$dbname, 
                      DBConfig::$dbuser, 
                      DBConfig::$dbpass, 
                      array(PDO::MYSQL_ATTR_INIT_COMMAND => "SET NAMES utf8")
                  );
        $db->setAttribute( PDO::ATTR_ERRMODE , PDO::ERRMODE_EXCEPTION );

        $sql = "SELECT 
                  tam_id, 
                  tam_province_id,
                  tam_province_code,
                  tam_district_id,
                  tam_district_code,
                  dis_name,
                  tam_code,
                  tam_name,
                  tam_revenue,
                  tam_ratio_poverty,
                  tam_gap_poverty,
                  tam_violence_poverty,
                  tam_lat,
                  tam_lon
                FROM ".DBConfig::$dbname.".tambon , ".DBConfig::$dbname.".district
                WHERE tam_province_code = :proc
                AND tam_district_code = :disc
                AND dis_code = :disc
                AND tam_district_code = dis_code
                ORDER BY tam_lon DESC";
        $stmt = $db->prepare($sql);
        $stmt->bindParam( ':proc', $proc, PDO::PARAM_INT );
        $stmt->bindParam( ':disc', $disc, PDO::PARAM_INT );

        $stmt->execute();
        $result = $stmt->fetchAll();

        //var_dump($result);
        TambonChart::renderPovertyRatioChart($result);

        /*** close the database connection ***/
        $db = null;
      }catch(PDOException $e) {
          $response['errorCode'] = 5;
          $response['errorString'] = "Database cann't connect";
          $response['errorString'] = $e->getMessage();
          //$this->setProvinceResponse($response);
      }
    }


    public static function renderPovertyRatioChart($result){
      include("gchart/tambonPovertyRatioChart.php");
    }



    public static function getPovertyGapChartByCode($proc,$disc){
      //Query Province  $votename, $country, $filter
      try {
        $db = new PDO("mysql:host=".DBConfig::$dbhost.";dbname=".DBConfig::$dbname, 
                      DBConfig::$dbuser, 
                      DBConfig::$dbpass, 
                      array(PDO::MYSQL_ATTR_INIT_COMMAND => "SET NAMES utf8")
                  );
        $db->setAttribute( PDO::ATTR_ERRMODE , PDO::ERRMODE_EXCEPTION );

        $sql = "SELECT 
                  tam_id, 
                  tam_province_id,
                  tam_province_code,
                  tam_district_id,
                  tam_district_code,
                  dis_name,
                  tam_code,
                  tam_name,
                  tam_revenue,
                  tam_ratio_poverty,
                  tam_gap_poverty,
                  tam_violence_poverty,
                  tam_lat,
                  tam_lon
                FROM ".DBConfig::$dbname.".tambon , ".DBConfig::$dbname.".district
                WHERE tam_province_code = :proc
                AND tam_district_code = :disc
                AND dis_code = :disc
                AND tam_district_code = dis_code
                ORDER BY tam_lon DESC";
                
        $stmt = $db->prepare($sql);
        $stmt->bindParam( ':proc', $proc, PDO::PARAM_INT );
        $stmt->bindParam( ':disc', $disc, PDO::PARAM_INT );
        $stmt->execute();
        $result = $stmt->fetchAll();

        //var_dump($result);
        TambonChart::renderPovertyGapChart($result);

        /*** close the database connection ***/
        $db = null;
      }catch(PDOException $e) {
          $response['errorCode'] = 5;
          $response['errorString'] = "Database cann't connect";
          $response['errorString'] = $e->getMessage();
          //$this->setProvinceResponse($response);
      }
    }


    public static function renderPovertyGapChart($result){
      include("gchart/tambonPovertyGapChart.php");
    }




    public static function getPovertyViolenceChartByCode($proc,$disc){
      //Query Province  $votename, $country, $filter
      try {
        $db = new PDO("mysql:host=".DBConfig::$dbhost.";dbname=".DBConfig::$dbname, 
                      DBConfig::$dbuser, 
                      DBConfig::$dbpass, 
                      array(PDO::MYSQL_ATTR_INIT_COMMAND => "SET NAMES utf8")
                  );
        $db->setAttribute( PDO::ATTR_ERRMODE , PDO::ERRMODE_EXCEPTION );

        $sql = "SELECT 
                  tam_id, 
                  tam_province_id,
                  tam_province_code,
                  tam_district_id,
                  tam_district_code,
                  dis_name,
                  tam_code,
                  tam_name,
                  tam_revenue,
                  tam_ratio_poverty,
                  tam_gap_poverty,
                  tam_violence_poverty,
                  tam_lat,
                  tam_lon
                FROM ".DBConfig::$dbname.".tambon , ".DBConfig::$dbname.".district
                WHERE tam_province_code = :proc
                AND tam_district_code = :disc
                AND dis_code = :disc
                AND tam_district_code = dis_code
                ORDER BY tam_lon DESC";
        $stmt = $db->prepare($sql);
        $stmt->bindParam( ':proc', $proc, PDO::PARAM_INT );
        $stmt->bindParam( ':disc', $disc, PDO::PARAM_INT );
        $stmt->execute();
        $result = $stmt->fetchAll();

        //var_dump($result);
        TambonChart::renderPovertyViolenceChart($result);

        /*** close the database connection ***/
        $db = null;
      }catch(PDOException $e) {
          $response['errorCode'] = 5;
          $response['errorString'] = "Database cann't connect";
          $response['errorString'] = $e->getMessage();
          //$this->setDistrictResponse($response);
      }
    }


    public static function renderPovertyViolenceChart($result){
      include("gchart/tambonPovertyViolenceChart.php");
    }


    public static function getRemarkChartByCode($proc,$disc){
      //Query Province  $votename, $country, $filter
      try {
        $db = new PDO("mysql:host=".DBConfig::$dbhost.";dbname=".DBConfig::$dbname, 
                      DBConfig::$dbuser, 
                      DBConfig::$dbpass, 
                      array(PDO::MYSQL_ATTR_INIT_COMMAND => "SET NAMES utf8")
                  );
        $db->setAttribute( PDO::ATTR_ERRMODE , PDO::ERRMODE_EXCEPTION );

        $sql = "SELECT 
                  tam_id, 
                  tam_province_code,
                  tam_district_code,
                  dis_name,
                  tam_code,
                  tam_name,
                  tam_lat,
                  tam_lon,
                  COUNT(per_id) as count_person
                FROM ".DBConfig::$dbname.".tambon , ".DBConfig::$dbname.".district , ".DBConfig::$dbname.".person
                WHERE tam_province_code = :proc
                AND tam_district_code = :disc
                AND dis_code = :disc
                AND tam_district_code = dis_code
                AND per_district_code = dis_code
                AND per_district_code = tam_district_code
                AND per_tambon_code = tam_code
                GROUP BY per_tambon_code
                ORDER BY tam_lon DESC";
        $stmt = $db->prepare($sql);
        $stmt->bindParam( ':proc', $proc, PDO::PARAM_INT );
        $stmt->bindParam( ':disc', $disc, PDO::PARAM_INT );
        $stmt->execute();
        $result = $stmt->fetchAll();

        //Remarkv = 1 (Selected)
        $sql = "SELECT 
                  tam_id, 
                  COUNT(per_id) as count_person
                FROM ".DBConfig::$dbname.".tambon , ".DBConfig::$dbname.".district , ".DBConfig::$dbname.".person
                WHERE tam_province_code = :proc
                AND tam_district_code = :disc
                AND dis_code = :disc
                AND tam_district_code = dis_code
                AND per_district_code = dis_code
                AND per_district_code = tam_district_code
                AND per_tambon_code = tam_code
                AND per_remarkv = 1
                GROUP BY per_tambon_code
                ORDER BY tam_lon DESC";
        $stmt = $db->prepare($sql);
        $stmt->bindParam( ':proc', $proc, PDO::PARAM_INT );
        $stmt->bindParam( ':disc', $disc, PDO::PARAM_INT );
        $stmt->execute();
        $selected = $stmt->fetchAll();

        //Remarkv = 0 (Not selected)
        $sql = "SELECT 
                  tam_id, 
                  COUNT(per_id) as count_person
                FROM ".DBConfig::$dbname.".tambon , ".DBConfig::$dbname.".district , ".DBConfig::$dbname.".person
                WHERE tam_province_code = :proc
                AND tam_district_code = :disc
                AND dis_code = :disc
                AND tam_district_code = dis_code
                AND per_district_code = dis_code
                AND per_district_code = tam_district_code
                AND per_tambon_code = tam_code
                AND per_remarkv = 0
                GROUP BY per_tambon_code
                ORDER BY tam_lon DESC";
        $stmt = $db->prepare($sql);
        $stmt->bindParam( ':proc', $proc, PDO::PARAM_INT );
        $stmt->bindParam( ':disc', $disc, PDO::PARAM_INT );
        $stmt->execute();
        $notselected = $stmt->fetchAll();

        //var_dump($result);
        TambonChart::renderRemarkChartByCode($result, $selected, $notselected);

        /*** close the database connection ***/
        $db = null;
      }catch(PDOException $e) {
          $response['errorCode'] = 5;
          $response['errorString'] = "Database cann't connect";
          $response['errorString'] = $e->getMessage();
          //$this->setDistrictResponse($response);
      }
    }


    public static function renderRemarkChartByCode($result, $selected, $notselected){

      //var_dump($selected);
      include("gchart/tambonRemarkChart.php");
    }
  }
?>